package id.co.shuba.spamobile.data;

import id.co.shuba.spamobile.model.StandardInfo;
import id.co.shuba.spamobile.model.ProspectInfo;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class StandardDB extends BaseDB {	
	/*
	public static final String OPTION_JOB = "Type of Business";
	public static final String OPTION_DEBTOR_TYPE = "Type of Debtor";
	public static final String OPTION_PROSPECT_TYPE = "Prospect Type";
	public static final String OPTION_CHANNELING = "Sales Channel";
	public static final String OPTION_EXTERNAL_TYPE = "External Type";
	public static final String OPTION_MARITAL_STATUS = "Marital Status";
	public static final String OPTION_OBJECTIVE_OF_VISIT = "Objective of Visit";
	public static final String OPTION_RESULT_OF_VISIT = "Result of Visit";
	public static final String OPTION_REASON = "Reason";
	public static final String OPTION_PRODUCT = "Product";
	public static final String OPTION_CHANNELING2 = "Sales Channel2";
	public static final String OPTION_PROGRESS_STATUS = "Progress Status";*/
	
	public static final String GROUP_JOB = "14";
	public static final String GROUP_DEBTOR_TYPE = "9";
	public static final String GROUP_PROSPECT_TYPE = "5";
	public static final String GROUP_CHANNELING = "7";
	public static final String GROUP_EXTERNAL_TYPE = "15";
	public static final String GROUP_MARITAL_STATUS = "3";
	public static final String GROUP_OBJECTIVE_OF_VISIT = "13";
	public static final String GROUP_RESULT_OF_VISIT = "11";
	public static final String GROUP_REASON = "12";
	public static final String GROUP_PRODUCT = "998";
	public static final String GROUP_CHANNELING2 = "999";
	public static final String GROUP_PROGRESS_STATUS = "4";
	
	
	public static final String MARITAL_STATUS_MARIED = "1002";
	
	public StandardDB(DatabaseHandler db) {
		super(db);
	}
	
	public static void addStandard(SQLiteDatabase db, String group, String value, String text, String type, String date, String isActive) {
		String[] params = new String[] { group, value, text, type, date, isActive };
		db.execSQL("INSERT INTO TBL_MS_STANDARD(STD_GROUP, STD_VALUE, STD_TEXT, TYPE, UPDATE_DATE, IS_ACTIVE) VALUES(?,?,?,?,?,?)", params);		
	}
	
	public static void addOrUpdateOption(SQLiteDatabase db, String group, String value,
			String text, String type, String date, String isActive) {
		String[] params = new String[] { group, value, text, type, date, isActive };
		db.execSQL("INSERT OR REPLACE INTO TBL_MS_STANDARD(STD_GROUP, STD_VALUE, STD_TEXT, STD_TYPE, UPDATE_DATE, IS_ACTIVE) VALUES(?,?,?,?,?,?)", params);		
	}
	
	public List<StandardInfo> getList(String group, String type) {
		String sql = "SELECT STD_VALUE AS _id, STD_TEXT FROM TBL_MS_STANDARD WHERE IS_ACTIVE = '1' AND STD_GROUP = ? AND STD_TYPE = ?";
		String[] params = new String[] { group, type };

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, params);

		List<StandardInfo> list = new ArrayList<StandardInfo>();

		if (cursor.moveToFirst()) {
			do {
				StandardInfo info = new StandardInfo(cursor.getString(0),
						cursor.getString(1));
				list.add(info);
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;
	}
	
	public List<StandardInfo> getList(String group) {
		String sql = "SELECT STD_VALUE AS _id, STD_TEXT FROM TBL_MS_STANDARD WHERE IS_ACTIVE = '1' AND STD_GROUP = ?";
		String[] params = new String[] { group };

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, params);

		List<StandardInfo> list = new ArrayList<StandardInfo>();

		if (cursor.moveToFirst()) {
			do {
				StandardInfo info = new StandardInfo(cursor.getString(0),
						cursor.getString(1));
				list.add(info);
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;
	}
	
	public List<StandardInfo> getExternalMappingList(String channelID) {
		List<StandardInfo> list = new ArrayList<StandardInfo>();
		//String sql = "SELECT STD_VALUE AS _id, STD_TEXT FROM TBL_MS_STANDARD WHERE STD_GROUP = ?";
		String sql = "SELECT A.STD_VALUE AS _id, A.STD_TEXT " + 
					"FROM TBL_MS_STANDARD A INNER JOIN " +
					"TBL_MS_EXTERNAL_MAPPING B ON A.STD_VALUE = B.EXTERNAL_ID " +
					"WHERE B.IS_ACTIVE = '1' AND A.IS_ACTIVE = '1' AND A.STD_GROUP = ? " +
					"AND B.CHANNEL_ID = ?";
		String[] params = new String[] { GROUP_EXTERNAL_TYPE, channelID  };

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, params);
		
		if (cursor.moveToFirst()) {
			do {
				StandardInfo info = new StandardInfo(cursor.getString(0),
						cursor.getString(1));
				list.add(info);
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;
	}
	
	public static String getMaxDate(SQLiteDatabase db) {		
		Cursor cursor = db.rawQuery(
				"SELECT MAX(UPDATE_DATE) UPDATE_DATE FROM TBL_MS_STANDARD WHERE STD_GROUP <> ? AND STD_GROUP <> ?",
				new String[] { String.valueOf(GROUP_PRODUCT), String.valueOf(GROUP_CHANNELING2) });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}
		
		String result = cursor.getString(cursor.getColumnIndex("UPDATE_DATE"));		
		cursor.close();

		return result;
	}
	
	public List<StandardInfo> getProductList(String type) {
		return getList(GROUP_PRODUCT, type);
	}
	
	public static String getMaxDateChannel(SQLiteDatabase db) {		
		Cursor cursor = db.rawQuery(
				"SELECT MAX(UPDATE_DATE) UPDATE_DATE FROM TBL_MS_STANDARD WHERE STD_GROUP = ?",
				new String[] { String.valueOf(GROUP_CHANNELING2) });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}
		
		String result = cursor.getString(cursor.getColumnIndex("UPDATE_DATE"));		
		cursor.close();

		return result;
	}	
	
	public static void addOrUpdateExternalMapping(SQLiteDatabase db, String externalID, String channelID, String date, String isActive) {
		String[] params = new String[] { externalID, channelID, date, isActive };
		db.execSQL("INSERT OR REPLACE INTO TBL_MS_EXTERNAL_MAPPING(EXTERNAL_ID, CHANNEL_ID, UPDATE_DATE,IS_ACTIVE) VALUES(?,?,?,?)", params);		
	}
	
	public static String getMaxDateExternalMapping(SQLiteDatabase db) {		
		Cursor cursor = db.rawQuery(
				"SELECT MAX(UPDATE_DATE) UPDATE_DATE FROM TBL_MS_EXTERNAL_MAPPING", null);

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}
		
		String result = cursor.getString(cursor.getColumnIndex("UPDATE_DATE"));		
		cursor.close();

		return result;
	}
	
	public static void deleteProduct(SQLiteDatabase db) {
		db.delete("TBL_MS_STANDARD", "STD_GROUP = ?", new String[] { GROUP_PRODUCT });
	}
	
	public static void deleteAll(SQLiteDatabase db) {
		db.delete("TBL_MS_STANDARD", "STD_GROUP <> ?", new String[] { GROUP_PRODUCT });
	}	
}
